package teaching.service.baseInfoManage.common;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

public class ExcelUtil {
	

	 public  static HSSFCellStyle setCellStyle(HSSFWorkbook wb){
		    Font font = wb.createFont();
		    font.setFontHeight((short)11);
		    font.setFontHeightInPoints((short)11);
		    HSSFCellStyle style = wb.createCellStyle();  	    
		    style.setFont(font);
//		    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//		    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
		    return style;
	 }
	 
	 /**
	  * 获取读取表格操作类
	  * @return
	  */
	 public ExcelReader getExcelReader(){
		 return  new ExcelReader();
	 }
	 
	 
	 /**
	  * 操作Excel表格的功能类
	  */
	 public  class ExcelReader {
	     private POIFSFileSystem fs;
	     private HSSFWorkbook wb;
	     private HSSFSheet sheet;
	     private HSSFRow row;

	     /**
	      * 读取Excel表格表头的内容
	      * @param InputStream
	      * @return String 表头内容的数组
	      */
	     public String[] readExcelTitle(InputStream is) {
	         try {
	             fs = new POIFSFileSystem(is);
	             wb = new HSSFWorkbook(fs);
	         } catch (IOException e) {
	             e.printStackTrace();
	         }
	         sheet = wb.getSheetAt(0);
	         row = sheet.getRow(0);
	         // 标题总列数
	         int colNum = row.getPhysicalNumberOfCells();
	         System.out.println("colNum:" + colNum);
	         String[] title = new String[colNum];
	         for (int i = 0; i < colNum; i++) {
	             //title[i] = getStringCellValue(row.getCell((short) i));
	             title[i] = getCellFormatValue(row.getCell(i));
	         }
	         return title;
	     }

	     /**
	      * 读取Excel数据内容
	      * @param InputStream
	      * @return Map 包含单元格数据内容的Map对象
	      */
     public List<Map<Integer,String>> readExcelContent(InputStream is) {
	      
	         try {
	             fs = new POIFSFileSystem(is);
	             wb = new HSSFWorkbook(fs);
	         } catch (IOException e) {
	             e.printStackTrace();
	         }
	         sheet = wb.getSheetAt(0);
	         // 得到总行数
	         int rowNum = sheet.getLastRowNum();
	        
	         row = sheet.getRow(0);
	         int colNum = row.getPhysicalNumberOfCells();
	         List<Map<Integer,String>> list = new ArrayList<Map<Integer,String>>();
	         // 正文内容应该从第二行开始,第一行为表头的标题
	         for (int i = 1; i <= rowNum; i++) {
	             row = sheet.getRow(i);
	             int j = 0;
	             Map<Integer,String> map = new HashMap<Integer, String>();
	             while (j < colNum) {
	            	 
	            	if(row.getCell(j)!=null&&!"".equals(row.getCell(j))){
	            		  map.put(j, getCellFormatValue(row.getCell(j)).trim());  
	            		  j++;
	            	}
	                           
	               
	             }
	             list.add(map);

	         }
	         return list;
	     }

     /**
      * 获取单元格数据内容为字符串类型的数据
      * 
      * @param cell Excel单元格
      * @return String 单元格数据内容
      */
	  @SuppressWarnings("unused")
	private String getStringCellValue(HSSFCell cell) {
	         String strCell = "";
	         switch (cell.getCellType()) {
	         case HSSFCell.CELL_TYPE_STRING:
	             strCell = cell.getStringCellValue();
	             break;
	         case HSSFCell.CELL_TYPE_NUMERIC:
	             strCell = String.valueOf(cell.getNumericCellValue());
	             break;
	         case HSSFCell.CELL_TYPE_BOOLEAN:
	             strCell = String.valueOf(cell.getBooleanCellValue());
	             break;
	         case HSSFCell.CELL_TYPE_BLANK:
	             strCell = "";
	             break;
	         default:
	             strCell = "";
	             break;
	         }
	         if (strCell.equals("") || strCell == null) {
	             return "";
	         }
	         if (cell == null) {
	             return "";
	         }
	         return strCell;
	     }

		 /**
		  * 获取单元格数据内容为日期类型的数据
		  * 
		  * @param cell
		  *            Excel单元格
		  * @return String 单元格数据内容
		  */
		   @SuppressWarnings({ "unused", "deprecation" })
		private String getDateCellValue(HSSFCell cell) {
	         String result = "";
	         try {
	             int cellType = cell.getCellType();
	             if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
	                 Date date = cell.getDateCellValue();
	                 result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
	                         + "-" + date.getDate();
	             } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
	                 String date = getStringCellValue(cell);
	                 result = date.replaceAll("[年月]", "-").replace("日", "").trim();
	             } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
	                 result = "";
	             }
	         } catch (Exception e) {
	             System.out.println("日期格式不正确!");
	             e.printStackTrace();
	         }
	         return result;
   }

		 /**
		  * 根据HSSFCell类型设置数据
		  * @param cell
		  * @return
		  */
		  private String getCellFormatValue(HSSFCell cell) {
	         String cellvalue = "";
	         if (cell != null) {
	             // 判断当前Cell的Type
	             switch (cell.getCellType()) {
	             // 如果当前Cell的Type为NUMERIC
	             case HSSFCell.CELL_TYPE_NUMERIC:
	            	  if (HSSFDateUtil.isCellDateFormatted(cell)) {
                          
		                     //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//		                     cellvalue = cell.getDateCellValue().toLocaleString();
//		                     System.out.println("日期"+cellvalue);
		                     //方法2：这样子的data格式是不带带时分秒的：2011-10-12
		                	
		                     Date date = cell.getDateCellValue();
		                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		                     cellvalue = sdf.format(date);
		                     
		                 }else{
		                	 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			            	 cellvalue = cell.getRichStringCellValue().getString(); 
		                 }
	            	
	            	
	            	 break;
	             case HSSFCell.CELL_TYPE_FORMULA: {
	                 // 判断当前的cell是否为Date
	                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
	                            
	                     //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//	                     cellvalue = cell.getDateCellValue().toLocaleString();
//	                     System.out.println("日期"+cellvalue);
	                     //方法2：这样子的data格式是不带带时分秒的：2011-10-12
	                	 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	                     Date date = cell.getDateCellValue();
	                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	                     cellvalue = sdf.format(date);
	                     
	                 }
	                 break;
	             }
	             // 如果当前Cell的Type为STRIN
	             case HSSFCell.CELL_TYPE_STRING:
	                 // 取得当前的Cell字符串
	                 cellvalue = cell.getRichStringCellValue().getString();
	                 break;
	             // 默认的Cell值
	             default:
	                 cellvalue = " ";
	             }
	         } else {
	             cellvalue = "";
	         }
	       
	         return cellvalue;

	     }
	 }


}
